package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.FittingDao;
import com.windfone.model.Fitting;
import com.windfone.model.Fittingtype;
import com.windfone.model.Fittingtypebase;

public class FittingDaoImpl implements FittingDao{
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
		this.jdbcTemplate = jdbcTemplate;
	}

	String DELETE_SQL = " DELETE FROM  fitting WHERE id=? ";
	public int deleteFitting(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_ALL_NUMBER_SQL = " SELECT COUNT(1) FROM  fitting f,fittingtype t,fittingtypebase b " +
			"WHERE f.fittingTypeId=t.id AND b.id = t.basetypeId  AND f.fittingTypeBaseId=b.id";
	public int getAllRecorderNumber(final String whereCondition) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = GET_ALL_NUMBER_SQL + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	

	String UPDATE_SQL = " UPDATE fitting SET typeName=? ,parameter=? ,otherProperties=? ,marketTime=?, " +
			" price=?,content=? ,fittingTypeId=?,fittingTypeBaseId=? ,imagepath=?,bigimagepath=?,standar=?,memo=?,service=? WHERE id=? ";
	public int updateFitting(final Fitting bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getTypeName());
						ps.setString(i++,bean.getParameter());
						ps.setString(i++,bean.getOtherProperties());
						ps.setString(i++,bean.getMarketTime());
						ps.setDouble(i++,bean.getPrice());
						ps.setString(i++,bean.getContent());
						ps.setInt(i++,bean.getFittingTypeId());
						ps.setInt(i++,bean.getFittingTypeBaseId());						
						ps.setString(i++,bean.getImagepath());
						ps.setString(i++,bean.getBigimagepath());
						ps.setString(i++,bean.getStandar());
						ps.setString(i++,bean.getMemo());
						ps.setString(i++,bean.getService());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String INSERT_SQL = " INSERT INTO fitting (typeName,parameter,otherProperties, marketTime,content,price,fittingTypeId,fittingTypeBaseId,imagepath,bigimagepath,standar,memo,service) " +
			"VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,? ) ";
	public int insertFitting(final Fitting bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getTypeName());
						ps.setString(i++,bean.getParameter());
						ps.setString(i++,bean.getOtherProperties());
						ps.setString(i++,bean.getMarketTime());
						ps.setString(i++,bean.getContent());
						ps.setDouble(i++,bean.getPrice());
						ps.setInt(i++,bean.getFittingTypeId());
						ps.setInt(i++,bean.getFittingTypeBaseId());
						ps.setString(i++,bean.getImagepath());
						ps.setString(i++,bean.getBigimagepath());
						ps.setString(i++,bean.getStandar());
						ps.setString(i++,bean.getMemo());
						ps.setString(i++,bean.getService());
						System.out.println(  bean.getService() );
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	String GET_BYID_SQL = " SELECT * FROM  fitting  WHERE id=? ";
	public List<Fitting> getFittingById(final int keyValue) {
		final List<Fitting> result = new ArrayList<Fitting>();
		try {
			jdbcTemplate.query(
					GET_BYID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Fitting to= new Fitting();
 							to.setId(rs.getInt("id"));
 							to.setTypeName(rs.getString("typeName"));
 							to.setParameter(rs.getString("parameter"));
 							to.setOtherProperties(rs.getString("otherProperties"));
 							to.setMarketTime(rs.getString("marketTime"));
 							to.setContent(rs.getString("content"));
 							to.setPrice(rs.getDouble("price"));
 							to.setFittingTypeBaseId(rs.getInt("fittingTypeBaseId"));
 							to.setFittingTypeId(rs.getInt("fittingTypeId"));
 							to.setImagepath(rs.getString("imagepath"));
 							to.setBigimagepath(rs.getString("bigimagepath"));
 							to.setService(rs.getString("service"));
 							to.setMemo(rs.getString("memo")); 							
 							to.setStandar(rs.getString("standar")); 							
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String GET_FITTING_BY_TYPEID_SQL = " SELECT * FROM  fitting  WHERE fittingTypeId=? ";
	public List<Fitting> getFittingByTypeId(final int fittingTypeId) {
		final List<Fitting> result = new ArrayList<Fitting>();
		try {
			jdbcTemplate.query(
					GET_FITTING_BY_TYPEID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, fittingTypeId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Fitting to= new Fitting();
							to.setId(rs.getInt("id"));
							to.setTypeName(rs.getString("typeName"));
							to.setParameter(rs.getString("parameter"));
							to.setOtherProperties(rs.getString("otherProperties"));
							to.setMarketTime(rs.getString("marketTime"));
							to.setContent(rs.getString("content"));
							to.setPrice(rs.getDouble("price"));
							to.setFittingTypeBaseId(rs.getInt("fittingTypeBaseId"));
							to.setFittingTypeId(rs.getInt("fittingTypeId"));
							to.setBigimagepath(rs.getString("bigimagepath"));
							to.setImagepath(rs.getString("imagepath"));
							to.setCreateTime(rs.getDate("createTime")); 
							to.setService(rs.getString("service"));
 							to.setMemo(rs.getString("memo")); 							
 							to.setStandar(rs.getString("standar"));							
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

//	String GET_ALL_SQL = "  SELECT * FROM  fitting  WHERE 1=1 ";
	String GET_ALL_SQL = " SELECT  f.* FROM fitting f,fittingtype t,fittingtypebase b " +
			"WHERE f.fittingTypeId=t.id AND b.id = t.basetypeId  AND f.fittingTypeBaseId=b.id";
	public List<Fitting> getAllFitting(int beginRecord,String whereCondition) {
		final List<Fitting> result = new ArrayList<Fitting>();
		String sql = GET_ALL_SQL + whereCondition + " ORDER BY id LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 							Fitting to= new Fitting();
							to.setId(rs.getInt("id"));
							to.setTypeName(rs.getString("typeName"));
							to.setParameter(rs.getString("parameter"));
							to.setOtherProperties(rs.getString("otherProperties"));
							to.setMarketTime(rs.getString("marketTime"));
							to.setContent(rs.getString("content"));
							to.setPrice(rs.getDouble("price"));
							to.setService(rs.getString("service"));
 							to.setMemo(rs.getString("memo")); 							
 							to.setStandar(rs.getString("standar"));							
							to.setCreateTime(rs.getDate("createTime"));
							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	String GET_ALL_TYPE_NUMBER_SQL = " SELECT COUNT(1) FROM  fittingtype    ";
	public int getAllTypeRecorderNumber() {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = GET_ALL_TYPE_NUMBER_SQL  ;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	
	String GET_ALL_TYPE_SQL = "  SELECT * FROM  fittingtype    ";
	public List<Fittingtype> getAllFittingType(int beginRecord) {
		final List<Fittingtype> result = new ArrayList<Fittingtype>();
		String sql = GET_ALL_TYPE_SQL + " ORDER BY id LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Fittingtype to= new Fittingtype();
						to.setId(rs.getInt("id"));
						to.setBasetypeId(rs.getInt("basetypeId"));
						to.setLangId(rs.getInt("langId"));
						to.setName(rs.getString("name"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	String GET_BY_TYPEID_SQL = " SELECT * FROM  fittingtype  WHERE id=? ";
	public List<Fittingtype> getFittingTypeById(final int keyValue) {
		final List<Fittingtype> result = new ArrayList<Fittingtype>();
		try {
			jdbcTemplate.query(
					GET_BY_TYPEID_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, keyValue);
					}
				},
				new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Fittingtype to= new Fittingtype();
							to.setId(rs.getInt("id"));
							to.setBasetypeId(rs.getInt("basetypeId"));
							to.setLangId(rs.getInt("langId"));
							to.setName(rs.getString("name"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	String GET_BY_BASETYPEID_SQL = " SELECT * FROM  fittingtype  WHERE basetypeId=? ";
	public List<Fittingtype> getFittingTypeByBaseTypeId(final int baseTypeId) {
		final List<Fittingtype> result = new ArrayList<Fittingtype>();
		try {
			jdbcTemplate.query(
					GET_BY_BASETYPEID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, baseTypeId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Fittingtype to= new Fittingtype();
							to.setId(rs.getInt("id"));
							to.setBasetypeId(rs.getInt("basetypeId"));
							to.setLangId(rs.getInt("langId"));
							to.setName(rs.getString("name"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String DELETE_TYPE_SQL = " DELETE FROM  fittingtype WHERE id=? ";
	public int deleteFittingType(final int fittingTypeId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_TYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, fittingTypeId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	
	private int id;
	private String name;
	private int basetypeId;
	private int langId;
	

	String INSERT_TYPE_SQL = " INSERT INTO fittingtype (name,basetypeId,langId) VALUES( ?,?,? ) ";
	public int insertFittingType(final Fittingtype bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				INSERT_TYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setInt(i++,bean.getBasetypeId());
						ps.setInt(i++,bean.getLangId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String UPDATE_TYPE_SQL = " UPDATE fittingtype SET name=? ,basetypeId=? ,langId=?  WHERE id=? ";
	public int updateFittingType(final Fittingtype bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				UPDATE_TYPE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setInt(i++,bean.getBasetypeId());
						ps.setInt(i++,bean.getLangId());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	String GET_BASE_SQL = " SELECT * FROM  fittingtypebase   ";
	public List<Fittingtypebase> getAllFittingtypebase() {
		final List<Fittingtypebase> result = new ArrayList<Fittingtypebase>();
		try {
			jdbcTemplate.query(
				GET_BASE_SQL,
				new RowCallbackHandler() { 
					public void processRow(ResultSet rs) throws SQLException {
						Fittingtypebase to= new Fittingtypebase();
						to.setId(rs.getInt("id"));
						to.setName(rs.getString("name"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public List<Fittingtype> getAllFittingType() {
		final List<Fittingtype> result = new ArrayList<Fittingtype>();
		String sql = GET_ALL_TYPE_SQL + " ORDER BY id " ;
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Fittingtype to= new Fittingtype();
						to.setId(rs.getInt("id"));
						to.setBasetypeId(rs.getInt("basetypeId"));
						to.setLangId(rs.getInt("langId"));
						to.setName(rs.getString("name"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<Fittingtype> getFittingTypeByBaseId(final int fittingtypebaseId) {
		final List<Fittingtype> result = new ArrayList<Fittingtype>();
		String sql = GET_ALL_TYPE_SQL+ " WHERE basetypeId=?" + " ORDER BY id  " ;
		try {
			 jdbcTemplate.query(
				sql,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, fittingtypebaseId);
					}
				},
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Fittingtype to= new Fittingtype();
						to.setId(rs.getInt("id"));
						to.setBasetypeId(rs.getInt("basetypeId"));
						to.setLangId(rs.getInt("langId"));
						to.setName(rs.getString("name"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private String SQL_GET_FITTING_FIRST_COMMEND_PAGE = " SELECT * FROM  fitting f,recommend r,recommendtype t WHERE f.id = r.recordId AND "+  
	 "t.id=r.recommendTypeId AND  t.name=? ";
	public List<Fitting> getCommendFittigByCommendType(final String commendType) {
		final List<Fitting> result = new ArrayList<Fitting>();
		try {
			 jdbcTemplate.query(
					 SQL_GET_FITTING_FIRST_COMMEND_PAGE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setString(1, commendType);
					}
				},
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
							Fitting to= new Fitting();
							to.setId(rs.getInt("id"));
							to.setTypeName(rs.getString("typeName"));
							to.setParameter(rs.getString("parameter"));
							to.setOtherProperties(rs.getString("otherProperties"));
							to.setMarketTime(rs.getString("marketTime"));
							to.setContent(rs.getString("content"));
							to.setImagepath(rs.getString("imagepath"));
							to.setBigimagepath(rs.getString("bigimagepath"));
							to.setPrice(rs.getDouble("price"));
							to.setCreateTime(rs.getDate("createTime"));
							to.setService(rs.getString("service"));
 							to.setMemo(rs.getString("memo")); 							
 							to.setStandar(rs.getString("standar"));
							result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

}
